package master.support;

import javax.persistence.Column;
import javax.persistence.Table;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;

import master.common.utils.ClassUtils;
import master.system.organization.Company;
import org.apache.commons.logging.LogFactory;
import org.apache.commons.logging.Log;
import org.springframework.beans.BeanUtils;

/**
 * SQL Generate, can generate insert/update/delete according entity class
 * support simple condition
 *
 * @author <a href="mailto:jfox.young@gmail.com">Young Yang</a>
 * @create Apr 27, 2008 10:58:31 AM
 */
public class SQLGenerator {

    final static Log log = LogFactory.getLog(SQLGenerator.class);

    //TODO: cache generated SQL with key: AUTO_%TABLE_NAME%_JOINCOLUMN_ID_ORDERID

    /**
     * build create sql
     *
     * @param entityClass
     * @return sql velocity template
     */
    public static String buildInsertSQL(Class<?> entityClass) {
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("INSERT INTO ").append(tableName).append(" (");
        StringBuffer sql2 = new StringBuffer(" VALUES (");

        int colIndex = 0;
        for (PropertyDescriptor propertyDescriptor : BeanUtils.getPropertyDescriptors(entityClass)) {
            if (propertyDescriptor.getName().equals("class")) {
                continue;
            }
            try {
                Field field = ClassUtils.getDeclaredField(entityClass, propertyDescriptor.getName());
                if (field.isAnnotationPresent(Column.class)) {
                    Column column = field.getAnnotation(Column.class);
                    if (colIndex > 0) {
                        sql.append(", ");
                        sql2.append(", ");
                    }
                    sql.append(column.name());
                    sql2.append(":").append(propertyDescriptor.getName());
                }
                colIndex++;
            }
            catch (NoSuchFieldException e) {
                throw new RuntimeException(e);
            }
        }

        sql.append(")");
        sql2.append(")");
        sql.append(sql2);
        return sql.toString();
    }

    public static String buildDeleteSQLById(Class<?> entityClass) {
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("DELETE FROM ").append(tableName).append(" WHERE ");
        sql.append("ID = ").append(":id");
        return sql.toString();
    }

    public static String buildDeleteSQLByWhere(Class<?> entityClass, String whereSql) {
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("DELETE FROM ").append(tableName);
        sql.append(" WHERE ").append(whereSql);
        return sql.toString();
    }

    public static String buildDeleteSQLByIdIn(Class<?> entityClass, long... ids) {
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("DELETE FROM ").append(tableName);
        sql.append(" WHERE ID IN (");
        int colIndex = 0;
        for (long id : ids) {
            if (colIndex > 0) {
                sql.append(", ");
            }
            sql.append(id);
            colIndex++;
        }
        sql.append(" )");
        return sql.toString();
    }

    public static String buildUpdateSQL(Class<?> entityClass) {
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("UPDATE ").append(tableName).append(" SET ");

        int colIndex = 0;
        for (PropertyDescriptor propertyDescriptor : BeanUtils.getPropertyDescriptors(entityClass)) {
            if (propertyDescriptor.getName().equals("class")) {
                continue;
            }
            if (!propertyDescriptor.getName().equals("ID")) {
                try {
                    Field field = ClassUtils.getDeclaredField(entityClass, propertyDescriptor.getName());
                    if (field.isAnnotationPresent(Column.class)) {
                        Column column = field.getAnnotation(Column.class);

                        if (colIndex > 0) {
                            sql.append(", ");
                        }
                        sql.append(column.name()).append(" = :").append(field.getName());
                        colIndex++;
                    }
                }
                catch (NoSuchFieldException e) {
                    throw new RuntimeException(e);
                }
            }

        }

        sql.append(" WHERE ").append("ID").append(" = :id");
        return sql.toString();
    }

    public static String buildSelectSQL(Class<?> entityClass) {
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("SELECT * FROM ").append(tableName);
        return sql.toString();
    }

    public static String buildSelectSQLById(Class<?> entityClass) {
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("SELECT * FROM ").append(tableName).append(" WHERE ID=:id");
        return sql.toString();
    }

    public static String buildSelectSQLByWhere(Class<?> entityClass, String whereSQL) {
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("SELECT * FROM ").append(tableName).append(" WHERE ").append(whereSQL);
        return sql.toString();
    }

    public static String buildSelectSQLByIdIn(Class<?> entityClass, long... ids) {
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("SELECT * FROM ").append(tableName);
        sql.append(" WHERE ID IN (");
        int colIndex = 0;
        for (long id : ids) {
            if (colIndex > 0) {
                sql.append(", ");
            }
            sql.append(id);
            colIndex++;
        }
        sql.append(" )");
        return sql.toString();
    }

    public static String buildSelectSQLByColumnIn(Class<?> entityClass,String column, Object... values) {
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("SELECT * FROM ").append(tableName);
        sql.append(" WHERE ").append(column).append(" IN (");
        int colIndex = 0;
        for (Object val : values) {
            if (colIndex > 0) {
                sql.append(", ");
            }
            sql.append(val);
            colIndex++;
        }
        sql.append(" )");
        return sql.toString();
    }

/*
    public static String buildSelectInByIdSQL(Class<?> entityClass, long... ids) {
        String pkColumnName = "ID";
        ColumnMetadata pkColumnMetadata = EntityRepository.getInstance().getPKColumnMetadata(entityClass);
        if (pkColumnMetadata != null) {
            pkColumnName = pkColumnMetadata.getName();
        }
        String[] colValues = new String[ids.length];
        for (int i = 0; i < ids.length; i++) {
            colValues[i] = ids[i] + "";
        }
        return buildSelectInByColumnSQL(entityClass, pkColumnName, colValues);
    }

    public static String buildSelectInByColumnSQL(Class<?> entityClass, String column, String... colValues) {
        String tableName = getTableName(entityClass);
        if (!EntityRepository.getInstance().isColumnExisted(entityClass, column)) {
            log.warn("Column " + column + " doesn't exist in Table " + tableName);
        }

        StringBuffer sql = new StringBuffer("SELECT * FROM ").append(tableName);
        if (colValues.length > 0) {
            sql.append(" WHERE ").append(column).append(" IN (");
            int colIndex = 0;
            for (String colValue : colValues) {
                if (colIndex > 0) {
                    sql.append(" , ");
                }
                sql.append(colValue);
                colIndex++;
            }
            sql.append(" )");
        }
        return sql.toString();
    }

    public static String buildSelectByConditoinSQL(Class<?> entityClass, Condition condition) {
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("SELECT * FROM ").append(tableName);
        if (condition != null) {
            sql.append(" WHERE ").append(condition.getSQLTemplateString());
        }
        return sql.toString();
    }

    public static String buildCountSQL(Class<?> entityClass) {
        return buildCountByConditionSQL(entityClass, null);
    }

    public static String buildCountByConditionSQL(Class<?> entityClass, Condition condition) {
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("SELECT COUNT(*) FROM ").append(tableName);
        if (condition != null) {
            sql.append(" WHERE ").append(condition.getSQLTemplateString());
        }
        return sql.toString();
    }
*/

    public static String getTableName(Class<?> entityClass) {
        return entityClass.getAnnotation(Table.class).name();
    }

    public static void main(String[] args) {
        System.out.println(SQLGenerator.buildInsertSQL(Company.class));
    }
}
